package com.personnel.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.personnel.db.C3p0Util1;
import com.personnel.entity.LzUser;

public class LzUserDao implements ILzUserDao{

	@Override
	public boolean addLzUser(LzUser lz) throws SQLException {
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = C3p0Util1.getConn();
			conn.setAutoCommit(false);
			String sql = " insert into t_rzlz(y_id,ygbh,ygxm,rzrq,lzrq,lzyy,lzqx,rck) " + 
					" values(?,?,?,?,?,?,?,?) ";
			pstm = conn.prepareStatement(sql);
			
			pstm.setInt(1, lz.getY_id());
			pstm.setInt(2, lz.getYgbh());
			pstm.setString(3, lz.getYgxm());
			pstm.setString(4, lz.getRzrq());
			pstm.setString(5, lz.getLzrq());
			pstm.setString(6, lz.getLzyy());
			pstm.setString(7, lz.getLzqx());
			pstm.setInt(8, lz.getRck());
			
			int x = pstm.executeUpdate();
			conn.commit();
			return x == 1;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			conn.rollback();
			conn.setAutoCommit(true);
			return false;
		}finally {
			C3p0Util1.close(pstm);
			C3p0Util1.close(conn);			
		}	
	}

	@Override
	public List<LzUser> queryAllLzUsers() throws SQLException {
		
		Connection conn = null;
		PreparedStatement pstm = null;
		
			conn = C3p0Util1.getConn();
			
			String sql = " select * from t_rzlz ";
			
			pstm = conn.prepareStatement(sql);
			ResultSet rs = pstm.executeQuery();
			ArrayList<LzUser> list = new ArrayList<>();
			while(rs.next()) {
				LzUser x = new LzUser();
				x.setY_id(rs.getInt("y_id"));
				x.setYgbh(rs.getInt("ygbh"));
				x.setYgxm(rs.getString("ygxm"));
				x.setRzrq(rs.getString("rzrq"));
				x.setLzrq(rs.getString("lzrq"));
				x.setLzyy(rs.getString("lzyy"));
				x.setLzqx(rs.getString("lzqx"));
				x.setRck(rs.getInt("rck"));
				list.add(x);
				
			}
			return list;
	}

	@Override
	public boolean deleteLzUser(int y_id) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = C3p0Util1.getConn();
			String sql = " delete from t_rzlz where y_id = ? ";
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, y_id);
			pstm.executeUpdate();
//			conn.commit();
			pstm.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			C3p0Util1.close(pstm);
			C3p0Util1.close(conn);
		}
		return false;
	}

}
